#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年11月9日

@author: chenyongbing
'''
import sys,os
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]

'''
服装：
select g.id,g.name,c.id,c.name from companies c, groups g where c.name in ("马克华菲（上海）商业有限公司","真维斯","赫基国际fiveplus","赫基国际trendiano","赫基国际ochirly","赫基国际covengarden","赫基国际miss sixty") and g.company_id = c.id and g.type=0;
32011262    总部    赫基国际Ochirly    98
32010390    总部    赫基国际FivePlus    73
32010416    总部    赫基国际Trendiano    75
32013721    总部    赫基国际CovenGarden    153
32014033    总部    赫基国际Miss sixty    155
32014267    总部    马克华菲（上海）商业有限公司    164
32016728    总部    真维斯    250

select g.id,g.name,c.id,c.name from companies c, groups g where c.name in ("上海欧美药妆","联想-雅诗兰黛","联想-欧莱雅（中国）","有礼派网络科技") and g.company_id = c.id and g.type=0;
32010334    68    有礼派网络科技
32012692    118    联想-欧莱雅（中国）
32013133    127    联想-雅诗兰黛

select g.id,g.name,c.id,c.name from companies c, groups g where c.name in ("上海欧美药妆","联想-雅诗兰黛","联想-欧莱雅（中国）","有礼派网络科技") and g.company_id = c.id and g.type=2 and g.name in ("北京","上海","深圳","沈阳","武汉","大连","济南","杭州");
32010343    武汉    68    有礼派网络科技
32013439    北京    68    有礼派网络科技
32015217    沈阳    68    有礼派网络科技
32015463    济南    68    有礼派网络科技
32012694    北京    118    联想-欧莱雅（中国）
32012728    上海    118    联想-欧莱雅（中国）
32013358    武汉    118    联想-欧莱雅（中国）
32013135    上海    127    联想-雅诗兰黛

select g.id,g.name,c.id,c.name from companies c, groups g where c.name in ("马克华菲（上海）商业有限公司","真维斯","赫基国际fiveplus","赫基国际trendiano","赫基国际ochirly","赫基国际covengarden","赫基国际miss sixty") and g.company_id = c.id and g.type=2 and g.name in ("北京","上海","深圳","沈阳","武汉","大连","济南","杭州");
32011264    北京    98    赫基国际Ochirly
32011272    上海    98    赫基国际Ochirly
32013734    深圳    98    赫基国际Ochirly
32013773    杭州    98    赫基国际Ochirly
32013853    大连    98    赫基国际Ochirly
32013857    武汉    98    赫基国际Ochirly
32013862    济南    98    赫基国际Ochirly
32014417    沈阳    98    赫基国际Ochirly
32013727    深圳    73    赫基国际FivePlus
32013741    上海    73    赫基国际FivePlus
32014046    杭州    73    赫基国际FivePlus
32014050    武汉    73    赫基国际FivePlus
32014151    大连    73    赫基国际FivePlus
32014155    北京    73    赫基国际FivePlus
32014303    济南    73    赫基国际FivePlus
32014306    沈阳    73    赫基国际FivePlus
32012149    上海    75    赫基国际Trendiano
32013745    武汉    75    赫基国际Trendiano
32013755    杭州    75    赫基国际Trendiano
32013790    沈阳    75    赫基国际Trendiano
32013794    大连    75    赫基国际Trendiano
32013802    北京    75    赫基国际Trendiano
32013826    深圳    75    赫基国际Trendiano
32014716    深圳    153    赫基国际CovenGarden
32016085    北京    153    赫基国际CovenGarden
32016320    杭州    153    赫基国际CovenGarden
32016616    大连    153    赫基国际CovenGarden
32016619    沈阳    153    赫基国际CovenGarden
32016629    武汉    153    赫基国际CovenGarden
32014040    北京    155    赫基国际Miss sixty
32014080    大连    155    赫基国际Miss sixty
32014083    沈阳    155    赫基国际Miss sixty
32014096    武汉    155    赫基国际Miss sixty
32014431    深圳    155    赫基国际Miss sixty
32015120    上海    155    赫基国际Miss sixty
32014269    上海    164    马克华菲（上海）商业有限公司
32016736    武汉    250    真维斯
32017369    上海    250    真维斯
'''



sys.path.append(os.path.join(current_dir,'../'))
from base.zhimind_datas import myZhimindDatas
from base.retail_datas import myRetailDatas
from base.time_method import myTimeMethod


comp_names1 = ["马克华菲（上海）商业有限公司","真维斯","赫基国际fiveplus","赫基国际trendiano","赫基国际ochirly","赫基国际covengarden","赫基国际miss sixty"]
comp_names2 = ["上海欧美药妆","联想-雅诗兰黛","联想-欧莱雅（中国）","有礼派网络科技"]

comp_names = comp_names1+comp_names2


city_names = ["北京","上海","深圳","沈阳","武汉","大连","济南","杭州"]

#city_names = ["上海"]

blackGroups = [32014484,32016948,32016953,32012725,32012712,32012739,32012765,32014282,32013139,32013138]

def get_groups_by_comp_and_group_name(comp_names = [],group_names = [],group_type=2):
    global blackGroups
    groups = []
    
    comp_names_query = '"'+'","'.join(comp_names)+'"'
    group_names_query = '"'+'","'.join(group_names)+'"'
    query = 'select g.id from groups g ,companies c where g.company_id = c.id and g.type=%s and c.name in (%s) and g.name in (%s)'%(group_type,comp_names_query,group_names_query)
    
    rets = myRetailDatas.myRetail.SelectAll(query)
    if rets == None:return groups
    for ret in rets:
        groups.append(int(ret[0]))
    shop_groups = myRetailDatas.get_shop_groups_by_group(groups)
    return list(set(shop_groups)-set(blackGroups))




def get_flow_and_cust(st='2015-10-10',et='2015-11-09',comp_names=[],city_names=[]):
    days = myTimeMethod.get_slist_between_st_et(st, et)
    datas = {}
    for day in days:
        datas[day] = {}
        flow_datas = []
        cust_datas = []
        for city_name in city_names:
            
            shop_groups = get_groups_by_comp_and_group_name(comp_names,[city_name])
            #客流量
            if len(shop_groups)==0:
                flow_cnt = 0
                cust_cnt = 0
            else:
                flow_cnt , cust_cnt = myZhimindDatas.get_target_from_rank_stats( timeType='day', groups=shop_groups, groupType=4, st=day, et=day, targetList=['sum(visit_cnt)','sum(ent_cust_cnt)'])
            flow_datas.append(str(flow_cnt))
            cust_datas.append(str(cust_cnt))
            #datas[day][city_name]  = [flow_cnt,cust_cnt]
        datas[day]['flow'] = flow_datas
        datas[day]['cust'] = cust_datas
    return datas


def get_new_and_old_cust(days=[],comp_names=[],city_names=[]):
    datas = {}
    for st,et in days:
        datas[st] = {}
        new_datas = []
        old_datas = []
        for city_name in city_names:
            shop_groups = get_groups_by_comp_and_group_name(comp_names,[city_name])
            
            if len(shop_groups)==0:
                new_cust_cnt = 0
                old_cust_cnt = 0
            else:
                new_cust_cnt , old_cust_cnt = myZhimindDatas.get_target_from_rank_stats( timeType='day', groups=shop_groups, groupType=4, st=st, et=et, targetList=['sum(new_cust)','sum(old_cust)'])
                if new_cust_cnt ==None:
                    new_cust_cnt = 0
                if old_cust_cnt == None:
                    old_cust_cnt = 0
            new_cust_cnt = int(new_cust_cnt)
            old_cust_cnt = int(old_cust_cnt)
            cust_cnt = new_cust_cnt + old_cust_cnt
            if cust_cnt == 0:
                new_cust_rate = '-'
                old_cust_rate = '-'
            else:
                new_cust_rate = str(round(float(new_cust_cnt)/cust_cnt,3))
                old_cust_rate = str(round(float(old_cust_cnt)/cust_cnt,3))
            new_datas.append(str(new_cust_rate))
            old_datas.append(str(old_cust_rate))
        datas[st]['new_cust_rate'] = new_datas
        datas[st]['old_cust_rate'] = old_datas
    return datas



def get_dur_rate(days=[],comp_names=[],city_names=[],role=0):
    datas = {}
    for st , et in days:
        datas[st] = {}
        dur_1_datas = []
        dur_2_datas = []
        dur_3_datas = []
        dur_4_datas = []
        for city_name in city_names:
            shop_groups = get_groups_by_comp_and_group_name(comp_names,[city_name])
            
            query = 'select  sum(dur_type_1_cnt) , sum(dur_type_2_cnt) , sum(dur_type_3_cnt) , sum(dur_type_4_cnt) ,sum(visit_freq) from mac_customer_stats  where day between "%s" and "%s" and dgroup in (%s)'%(st,et,','.join(map(lambda group:str(group),shop_groups)))
            #query = 'select  sum(dur_type_1_cnt) , sum(dur_type_2_cnt) , sum(dur_type_3_cnt) , sum(dur_type_4_cnt) ,sum(visit_freq) from mac_customer_stats  where day between "%s" and "%s" and dgroup in (%s)'%(st,et,','.join(map(lambda group:str(group),shop_groups)))
            
            if role==3:
                query+= ' and first_day=day'
            elif role==10:
                query+= ' and first_day!=day'
            logging.error(query)
            ret = myZhimindDatas.myZhimind.SelectOne(query)
            if ret == None or ret[0]==None or ret[1]==None or ret[2]==0 or ret[3]==None:
                dur_type_1_cnt = 0
                dur_type_2_cnt = 0
                dur_type_3_cnt = 0
                dur_type_4_cnt = 0
                cust_cnt = 0
            else:
                
                dur_type_1_cnt = int(ret[0]) 
                dur_type_2_cnt = int(ret[1])
                dur_type_3_cnt = int(ret[2])
                dur_type_4_cnt = int(ret[3])
                cust_cnt = int(ret[4])
            
            if cust_cnt == 0:
                dur_type_1_rate = '-'
                dur_type_2_rate = '-'
                dur_type_3_rate = '-'
                dur_type_4_rate = '-'
            else:
                dur_type_1_rate = str(round(dur_type_1_cnt/float(cust_cnt),3))
                dur_type_2_rate = str(round(dur_type_2_cnt/float(cust_cnt),3))
                dur_type_3_rate = str(round(dur_type_3_cnt/float(cust_cnt),3))
                dur_type_4_rate = str(round(dur_type_4_cnt/float(cust_cnt),3))
#             dur_1_datas.append(dur_type_1_rate)
#             dur_2_datas.append(dur_type_2_rate)
#             dur_3_datas.append(dur_type_3_rate)
#             dur_4_datas.append(dur_type_4_rate)
            dur_1_datas.append(str(dur_type_1_cnt))
            dur_2_datas.append(str(dur_type_2_cnt))
            dur_3_datas.append(str(dur_type_3_cnt))
            dur_4_datas.append(str(dur_type_4_cnt))
        datas[st]['1'] = dur_1_datas
        datas[st]['2'] = dur_2_datas
        datas[st]['3'] = dur_3_datas
        datas[st]['4'] = dur_4_datas
    return datas


def get_dur_count(days=[],comp_names=[],city_names=[],role=0):
    datas = {}
    for st , et in days:
        datas[st] = {}
        dur_1_datas = []
        dur_2_datas = []
        dur_3_datas = []
        dur_4_datas = []
        shop_groups = []
        shop_groups += get_groups_by_comp_and_group_name(comp_names,city_names)
            
        query = 'select  sum(dur_type_1_cnt) , sum(dur_type_2_cnt) , sum(dur_type_3_cnt) , sum(dur_type_4_cnt) ,sum(visit_freq) from mac_customer_stats  where day between "%s" and "%s" and dgroup in (%s)'%(st,et,','.join(map(lambda group:str(group),shop_groups)))
        #query = 'select  sum(dur_type_1_cnt) , sum(dur_type_2_cnt) , sum(dur_type_3_cnt) , sum(dur_type_4_cnt) ,sum(visit_freq) from mac_customer_stats  where day between "%s" and "%s" and dgroup in (%s)'%(st,et,','.join(map(lambda group:str(group),shop_groups)))
        
        if role==3:
            query+= ' and first_day=day'
        elif role==10:
            query+= ' and first_day!=day'
        logging.error(query)
        ret = myZhimindDatas.myZhimind.SelectOne(query)
        if ret == None or ret[0]==None or ret[1]==None or ret[2]==0 or ret[3]==None:
            dur_type_1_cnt = 0
            dur_type_2_cnt = 0
            dur_type_3_cnt = 0
            dur_type_4_cnt = 0
            cust_cnt = 0
        else:
            
            dur_type_1_cnt = int(ret[0]) 
            dur_type_2_cnt = int(ret[1])
            dur_type_3_cnt = int(ret[2])
            dur_type_4_cnt = int(ret[3])
            cust_cnt = int(ret[4])


        datas[st]['1'] = str(dur_type_1_cnt)
        datas[st]['2'] = str(dur_type_2_cnt)
        datas[st]['3'] = str(dur_type_3_cnt)
        datas[st]['4'] = str(dur_type_4_cnt)
    return datas

import logging
#logging.basicConfig(level = logging.INFO)





if __name__=="__main__":
    days = [['2015-11-07','2015-11-08'],
            ['2015-10-31','2015-11-01'],
             ['2015-10-24','2015-10-25'],
             ['2015-10-17','2015-10-18']]
#     days = [['2015-11-07','2015-11-08'],
#             ['2015-10-17','2015-10-18']
#             ]
    
    print '品类对比'
     
    #for my_comp_names in [['赫基国际trendiano'],['赫基国际fiveplus'],comp_names2]:
    for my_comp_names in [['赫基国际fiveplus']]:     
        for role in [3,10]:
             
             
            datas = get_dur_count(days= days ,comp_names = my_comp_names , city_names= ['北京','上海','深圳'],role=role)
            for day , dayDatas in datas.items():
                 
                print '\t'.join([day,dayDatas['1'],dayDatas['2'],dayDatas['3'],dayDatas['4']])
                
              
                
    print '顾客兴趣'
            
    datas = get_dur_rate(days= days ,comp_names = comp_names , city_names= city_names)
     
    for day , dayDatas in datas.items():
        print '\t'.join([day,'20+']+dayDatas['4'])
        print '\t'.join([day,'10-20']+dayDatas['3'])
        print '\t'.join([day,'5-10']+dayDatas['2'])
        print '\t'.join([day,'5-']+dayDatas['1'])
        
        
    print '顾客构成'
    datas = get_new_and_old_cust(days= days ,comp_names = comp_names , city_names= city_names)
    for day , dayDatas in datas.items():
        print '\t'.join([day]+dayDatas['new_cust_rate'])
        print '\t'.join([day]+dayDatas['old_cust_rate'])
          
         
        
        
          
if __name__=="__main__":
    i=0
    for comp_names in [comp_names1+comp_names2,['赫基国际trendiano'],['赫基国际fiveplus'],comp_names2]:
        i+=1
        datas = get_flow_and_cust(comp_names=comp_names,city_names=city_names)
        if i==1:
            #客流 +  入店
            print '\n\n客流趋势'
            print '\t'.join(['日期']+city_names)
            for day , dayDatas in datas.items():
                print '\t'.join([day]+dayDatas['flow'])
                  
            print '\n\n入店趋势'
            print '\t'.join(['日期']+city_names)
            for day , dayDatas in datas.items():
                print '\t'.join([day]+dayDatas['cust'])   
              
                  
        if i==2:
            #男装入店
            print '\n\n男装入店趋势'
            print '\t'.join(['日期']+city_names)
            for day , dayDatas in datas.items():
                print '\t'.join([day]+dayDatas['cust'])  
        if i==3:
            #女装入店
            print '\n\n女装入店趋势'
            print '\t'.join(['日期']+city_names)
            for day , dayDatas in datas.items():
                print '\t'.join([day]+dayDatas['cust'])  
        if i==4:
            #化妆品入店
            print '\n\n化妆品入店趋势'
            print '\t'.join(['日期']+city_names)
            for day , dayDatas in datas.items():
                print '\t'.join([day]+dayDatas['cust'])  
        
        
        
    

          
        
        


    



